package main.reports;

import main.SQL.MsSQLConnect;
import main.Trees.ProjectNode;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class XLSSumTableDataReport {
    private HashMap<String, CellStyle> styles;
    private ProjectNode curProject;
    private String pathXLSFile;
    private Connection connSQL;
    private Boolean defSelection;
    private ArrayList<Object[]> data;
    HSSFWorkbook wb;
    HSSFSheet sheet;
    int rownum;
    int sheetCount;

    private HashMap<String, String> hashSector, hashBlock, hashWorkType;

    public String generate (ProjectNode curProject, Connection connSQL, Boolean defSelection) {
        this.curProject = curProject;
        this.connSQL = connSQL;
        this.defSelection = defSelection;
        chooseFile();
        return pathXLSFile;
    }

    private void chooseFile() {
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("Выберите путь для сохранения файла");
        fileChooser.setApproveButtonText("Сохранить");
        File fle = new File("Исходные данные для сводных таблиц "+curProject.getShortName().replace("/", "-").replace("\\", "-")+".xls");
        fileChooser.setSelectedFile(fle);

        FileFilter filter = new FileNameExtensionFilter("Файлы Excel - XLS", "xls");
        fileChooser.setFileFilter(filter);
        String homeDir = new File("").getAbsolutePath();
        fileChooser.setCurrentDirectory(new File(homeDir));
        if (fileChooser.showSaveDialog(null) == JFileChooser.APPROVE_OPTION) {
            pathXLSFile = fileChooser.getSelectedFile().getAbsolutePath();
            if(!pathXLSFile.toLowerCase().endsWith(".xls"))
            {
                pathXLSFile = pathXLSFile + ".xls";
            }
            generateXLS();
        }
    }

    private void addTitle () {
        //region Первая строка заголовка
        Row row = sheet.createRow(rownum++);
        row.setHeightInPoints(14.00f);
        Cell cell;

        cell = row.createCell(0);
        cell.setCellValue("ID проекта");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(1);
        cell.setCellValue("Наименование проекта");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(2);
        cell.setCellValue("РН: ВИД РАБОТ <ЗАВОД>");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(3);
        cell.setCellValue("Имя сектора");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(4);
        cell.setCellValue("Имя блока");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(5);
        cell.setCellValue("ID работы");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(6);
        cell.setCellValue("Имя работы");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(7);
        cell.setCellValue("% выполнения");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(8);
        cell.setCellValue("ФО план");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(9);
        cell.setCellValue("ФО факт");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(10);
        cell.setCellValue("Тр-ты план");
        cell.setCellStyle(styles.get("main_style"));

        cell = row.createCell(11);
        cell.setCellValue("Тр-ты факт");
        cell.setCellStyle(styles.get("main_style"));

        //endregion
    }

    private void createStyles(){
        styles = new HashMap<>();

        CellStyle style;

        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex((short)57, (byte)217, (byte)217, (byte)217);

        HSSFFont mainFont = wb.createFont();
        mainFont.setFontHeightInPoints((short)9);
        mainFont.setFontName("Calibri");

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(mainFont);
        styles.put("main_style", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        HSSFCreationHelper creationHelper = wb.getCreationHelper();
        style.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.00"));
        style.setFont(mainFont);
        styles.put("sm_double", style);
    }

    private void generateXLS() {

        wb = new HSSFWorkbook();
        sheetCount = 1;
        sheet = wb.createSheet("Лист1");
        rownum = 0;

        sheet.setRowSumsBelow(false);

        createStyles();
        addTitle();
        connSQL = MsSQLConnect.ConnectDB("Factage"); //ssqlphn1d

        data = new ArrayList<Object[]>();
        fillRows();

        try {
            connSQL.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        getRow(curProject);

        //region Устанавливаем ширины столбцов в Excel
        sheet.setColumnWidth(0, 256*11);
        sheet.setColumnWidth(1, 256*30);
        sheet.setColumnWidth(2, 256*30);
        sheet.setColumnWidth(3, 256*30);
        sheet.setColumnWidth(4, 256*30);
        sheet.setColumnWidth(5, 256*11);
        sheet.setColumnWidth(6, 256*20);
        sheet.setColumnWidth(7, 256*11);
        sheet.setColumnWidth(8, 256*11);
        sheet.setColumnWidth(9, 256*11);
        sheet.setColumnWidth(10, 256*11);
        sheet.setColumnWidth(11, 256*11);
        //endregion

        FileOutputStream out;
        try {
            out = new FileOutputStream(new File(pathXLSFile));
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void addRow(Object[] dataRow) throws SQLException {

        Row row = sheet.createRow(rownum++);
        row.setHeightInPoints(14.00f);
        Cell cell;

        for (int j=0; j<12; j++) {
            cell = row.createCell(j);
            if (j < 8) {
                cell.setCellValue(dataRow[j].toString());
            } else {
                cell.setCellValue(Double.valueOf(dataRow[j].toString()));
            }

            cell.setCellStyle(styles.get(j < 8 ? "main_style" : "sm_double"));
        }

    }

    private void fillRows() {
        hashSector = new HashMap<>();
        hashBlock = new HashMap<>();
        hashWorkType = new HashMap<>();
        try {
            CallableStatement stmt;

            stmt = connSQL.prepareCall( "{call getReportDataSize( ?, ? )}" );
            stmt.setString( 1, curProject.getDbName() );
            stmt.setInt( 2, curProject.getId() );
            ResultSet rs = stmt.executeQuery();
            int count = 0;

            while ( rs.next() ) {
                count = rs.getInt("task_count");
            }

            System.out.println("Count = " + count);

            if (defSelection) {
                stmt = connSQL.prepareCall( "{call getReportData( ?, ? )}" );
            } else {
                stmt = connSQL.prepareCall( "{call getReportDataFilter( ?, ? )}" );
            }

            stmt.setString( 1, curProject.getDbName() );
            stmt.setInt( 2, curProject.getId() );

            rs = stmt.executeQuery();

            int i = 1;
            while ( rs.next() ) {
                i++;
                data.add(new Object[] {rs.getString("proj_short_name"), rs.getString("proj_name"), rs.getString("act_code_npz_descr"), rs.getString("parent_sector_code_desc"), rs.getString("sector_code_desc"), rs.getString("task_code"), rs.getString("task_name"), rs.getString("task_complete_pct"), rs.getInt("pv_plan"), rs.getInt("pv_fact"), rs.getInt("t_plan"), rs.getInt("t_fact")});
                if (rs.getString("parent_sector_code_desc").equals(rs.getString("proj_name"))) {
                } else {
                    hashSector.put(rs.getString("parent_sector_code_desc"), rs.getString("parent_sector_code_desc"));
                    hashBlock.put(rs.getString("sector_code_desc"), rs.getString("sector_code_desc"));
                    hashWorkType.put(rs.getString("act_code_npz_descr"), rs.getString("act_code_npz_descr"));
                }
           }

            for (Object[] dataRow : data) {
                addRow(dataRow);
            }
            System.out.println("Сектора: ");
            for (String sector : hashSector.values()) {
                System.out.println(sector);
            }
            System.out.println("Блоки: ");
            for (String block : hashBlock.values()) {
                System.out.println(block);
            }
            System.out.println("Работы: ");
            for (String work : hashWorkType.values()) {
                System.out.println(work);
            }

            rs.close();
            stmt.close();
        } catch (SQLException e) { e.printStackTrace(); }
    }
}
